Report Generator Functions and Parameters

Function	Use	Parameters	Example
ALLOCATETO	Returns a list of POS orders, backorders or jobs that this order is allocated to
ALLREORDPNT	Calculates the optimal stock holding for Understock/Overstock Reports
ALLSUGGACT	Returns the suggested action for Understock/Overstock Reports
ALLSUGGQTY	Returns the suggested quantity for Understock/Overstock Reports
AMTTOPAY	Returns the amount to pay in the Creditors Payment Report
AUTOLINEREF
BASEPRICEEX	Returns the pre-discount pre-tax price (the base selling price exclusive of GST and discounts). This is useful if you have an inc GST price and want to see the ex GST price before discount.
BC3OF9	Returns a string prefixed with an asterisk (*) for use with a type 3 of 9 barcode
BPAYREFERENCE	Returns a valid BPAY reference number	DBT_BPAYREF INVOICENO - optional depending on whether Micronet is set to use the invoice number when generating BPay references	BPAYREFERENCE(DBT_BPAYREF, INVOICENO)
BUDGETVAL	Returns the correct budget value for a particular month index	BUDGETVAL(amt, index)	BUDGETVAL(DBUD_SALESBUD0, 0)
BUDGETYTD	Calculates the year to date value of a budget figure, based on the current date		BUDGETYTD(DBUD_SALESBUD0)
BUDTHISMONTH	Returns the salesperson's budget for the current month	BUDTHISMONTH()
CALCCOST	Calculates the cost of an item (cost x quantity) taking into account packs per carton and purchase units	CALCCOST(cost, qty)	CALCOST(DIL_QTYDEL, DIL_COST)
CALCDISCOUNT	Calculates the discount on the Creditors Payment Due Report
CALCPURCHASEQTY	Calculates the purchase quantity taking into account purchasing units		CALCPURCHASEQTY(PHL_QTYORD)
CARTONS	Returns the total number of cartons (used of packs per carton items)		CARTONS(DIL_QTYDEL)
CHQAMT	Returns the cheque total for the cheque returned by CHQNO	CHQAMT(i)
CHQNO	Returns a list of cheque numbers used on a statement	CHQNO(i)	CHQNO(0) is the first cheque, CHQNO(1) is the second cheque, etc.
CODABAR	Returns a CODABAR formatted barcode	CODABAR(s)
COMM	Calculates a salesperson's commission on the Commission Report		COMM(DTRN_SALES)
CONTAINS	Returns TRUE if a field contains a string		CONTAINS(S1,S2) returns 1 when S2 is contained in S1
CONTRACT	Returns the contract information for a purchase order	CONTRACT(1..19) CONTRACT(1..5) = Discounts 1..5 CONTRACT(6/7) = Rebates 1 or 2 CONTRACT(8) = Settlement Discount CONTRACT(9) = Total Discounts CONTRACT(11..15) = Discounts 1..5 accumulated for purchase order CONTRACT(16/17) = Rebates 1 or 2 accumulated for purchase order CONTRACT(18) = Settlement Discount CONTRACT(19) = Total discounts accumulated for purchase order
COURIERBOXBC	Specialised BOX courier format
CURNO	Breaks up the current table for the ATB in MDS and MCL	CURNO(i)	CURNO(0) returns the first exchange rate found in the debtors/creditors transaction file
DATEDUE	Calculates the due date on the Creditors Payment Report and the due date for an MDS invoice
DATEFMT	Returns the number as a date in DD/MM/YY format
DEBTDUEPERIOD	Returns the current month and year in abbreviated format, e.g. MAR 2010
DEBTORITEMPART	Returns the debtor's part number or the item number if no debtor part exists		DEBTORITEMPART(DIL_ITMNO, DOH_DBTNO)
DEBTORPART	Returns the debtor's part number		DEBTORPART(DIL_ITMNO, DOH_DBTNO)
DEBTPER30DAYS	Returns the debtor's period number based on 30 day periods
DEBTPERIOD	Calculates the debtor's period number based on today's date and the invoice date
DEBTPERTERM	Returns the debtor's period number based on the debtor's sales terms (rather than invoice terms)
DELAMTREMAINING	Returns the amount remaining to be paid on an order
DELIVERYADDRESS	Returns the delivery address for a line of an order
DISCPERITEM	Within an invoicing operation, returns the discount that a customer receives ($ value each). Can be used on purchase orders.
DREORDPNT	Returns the reorder point calculated for the Dead Stock Report
DSUGGACT	Returns the suggested action for the Dead Stock Report
DSUGGQTY	Returns the suggested quantity for the Dead Stock Report
DUTYEXPERITEM	Returns the duty and excise per unit sell
DUTYEXTOT	Returns the total amount of duty and excise
EAN13	Returns a barcode in EAN13 format. Works only with the specific EAN13 barcode font supplied by Micronet (ean-13.ttf). You must assign the barcode font to the calculation field on the report. If the EAN13 barcode only has 12 characters, a check digit will be added.	EAN13(s)	EAN13(ITM_BC)
EANCHECKDIGIT	Returns a list of assigned bin locations
ELAPSEDTIMEFMT	Calculates and formats the elapsed time	ELAPSEDTIMEFMT(secs)
EORDERSTATUS	Returns the electronic order status
FREESTOCK	Returns the free stock amount of an item		FREESTOCK(ITM_ONHAND)
GETCOMP	Returns a field from the current company	GETCOMP(f)	GETCOMP(COMP_NAME) returns the current company's name GETCOMP(COMP_FIFOPERITEM) GETCOMP(XC_INVOICE0)
GETCUSTDEBT
GETDEBTD	Returns a field from a debtor		GETDEBTD(DTRN_DBTSALE, DBTNAME)
GETDELIVERYOPTIONS	Returns the name of the delivery option from enPOS	GETDELIVERYOPTIONS(posl_delopt)
GETDEPTNAME	Returns the current department name		GETDEPTNAME(GLACC_ACCOUNT)
GETDUTY	Returns the duty percentage		GETDUTY(ITM_DUTYRATE)
GETGLMONTHNAMES	Returns the month name from the current month, e.g. OCTOBER
GETGLMONTHNAMESYR	Returns the month name and year from the current month, e.g. OCTOBER 2010
GETGLPERIODNAME	Returns the GL period name from the base GL period number, e.g. Aug 04	GETGLPERIODNAME(PeriodNo)
GETITEMD
GETITEMFIELD
GETITEMUNIT1	Returns the first tiles/boxes for the item and value passed in
GETITEMUNIT2	Returns the second tiles/boxes for the item and value passed in
GETITEMUNIT3	Returns the third tiles/boxes for the item and value passed in
GETMONTHNAME	Returns the month name offset from the current month	GETMONTHNAME(n)
GETMULTIBINS	Returns a list of bin locations		GETMULTIBINS(DIL_SERLINK)
GETMULTIBINTEXT	Returns the specified text line related to multi bin picking slips in bin order	GETMULTIBINTEXT(DIL_SERLINK, #) where # is the number of the text line (i.e. 1 for first text line after item line, 2 for second text line, etc)	GETMULTIBINTEXT(DIL_SERLINK, 1); GETMULTIBINTEXT(DIL_SERLINK, 2) would print both text lines if there were two text lines related to one item line. If there is only one text line, the second text line will not print.
GETNARRATION	Gets a narration from the narration file
GETPERIODNAME	Returns the period name from the start of the financial year		GETPERIODNAME(0) returns the period name of the first financial period of the year
GETPERIODVALUE	Returns the period value from the start of the financial year		GETPERIODVALUE(DBT_SALE0, 0) gets the debtor sales value for the first period of the financial year
GETRATENAME	Returns the overtime rate name	GETRATENAME(r)	GETRATENAME(JT_SELECT)
GETSQLFIELD
GETSTAX	Returns the tax percentage. Looks at the system parameters to see if tax tables are turned on.		GETSTAX(ITM_STAX, ITM_STAXPERCENT)
GETUSER	Returns a field for the current user ID	GETUSER(f)	GETUSER(USERID_SUPERVISOR)
GETWEEKNUMBER	Returns the week number of the year based on the date	GETWEEKNUMBER(date)
GETWITM	Returns a value from the Warehouse Item file	GETWITM(itm,wh,field)	GETWITM("123","W1",WITM_ONHAND)
GLISCURPL	Returns TRUE if this account is the current year Profit and Loss account
GLISUNAPPPY	Returns TRUE if this account is the unappropriated Profit and Loss account
GLLYBALANCE	Returns last year's GL balance amount
GLLYBALANCETOPER	Returns last year's GL balance up to the current period
GLLYBUDGET	Returns last year's GL budget amount
GLLYBUDGETTOPER	Returns last year's GL budget up to the current period
GLLYMTDBALANCE	Returns the month to date GL balance for the current period last year
GLMTDBALANCE	Returns the month to date GL balance amount for the current period
GLMTDBUDGET	Returns the month to date GL budget amount for the current period
GLMTDPERCENT	Returns the month to date GL percentage value in the General Ledger profit and loss
GLOPENBALANCE	Returns the opening balance for the selected period in GL Account Inquiry reports
GLVALUEPERIOD	Returns the GL value for a given period	GLVALUEPERIOD(n)
GLYTDBALANCE	Returns the GL balance amount for the year to date
GLYTDBUDGET	Returns the GL budget amount for the year to date
GLYTDPERCENT	Returns the year to date GL percentage value in the General Ledger profit and loss
GP	Returns the gross profit as a percentage	GP(sale, cost)
GPREVERSE	Returns the sell price based on the gross profit percentage and cost	GPREVERSE(gp, cost)
GrandTotal	Returns the total invoice amount including subtotal, tax, freight, additional charges, etc.
GSTPERIOD	Returns the GST period name		GSTPERIOD(GST_PERIOD)
IMPORTCOST	Returns import costing information for the Import Costing Report	IMPORT COST(1..28) IMPORT COST(1) = shipment base IMPORT COST(2) = price in $AUS IMPORT COST(3) = price in $AUS at duty ex rate IMPORT COST(4) = duty in $AUS IMPORT COST(5) = value used to appropriate expenses (e.g. weight, volume or price) IMPORT COST(6) = item value used in appropriation IMPORT COST(7) = extra charges in $AUS IMPORT COST(8) = tax base IMPORT COST(9) = sales tax IMPORT COST(10) = landed cost IMPORT COST(11) = loading IMPORT COST(12) = total shipment value IMPORT COST(13) = total value in $AUS IMPORT COST(14) = total value in $AUS at duty ex rate IMPORT COST(15) = total duty IMPORT COST(16) = total value used to appropriate expenses IMPORT COST(17) = total value used in appropriation IMPORT COST(18) = total extra charges IMPORT COST(19) = total tax base IMPORT COST(20) = total sales tax IMPORT COST(21) = total landed cost IMPORT COST(22) = total loading IMPORT COST(23) = price in $AUS per each IMPORT COST(24) = duty in $AUS per each IMPORT COST(25) = extra charges in $AUS per each IMPORT COST(26) = sales tax in $AUS per each IMPORT COST(27) = landed cost per each IMPORT COST(28) = loading per each IMPORT COST(29) = total charges converted to $AUS
INCGST	Converts amount to including GST		INCGST(DIH_FREIGHT)
JCCOSTCENTER
KITEXPANDEDQTY	Calculates the expanded quantity of a multi level kit component
LEFTSTRING	Returns the first n characters of a string	LEFTSTRING(s, n)
LEFTZEROFILL	Zero fill string to the left
LEVY	Returns the levy amount for an invoice or line item. Can be used on purchase orders.	Accepts a single parameter - either 0 or 1. LEVY(0) = total levy for an invoice LEVY(1) = total levy for a particular line item	LEVY(0) LEVY(1)
LONGPERIODNAMES	Returns the GL period in a long format, e.g. January 2010	LONGPERIODNAMES(period number)
MAINTCONTRACTEXP	Returns the expiry date for a maintenance contract
MIDSTRING	Returns the ith character of s and then the rest of the string	MIDSTRING(s, i)	MIDSTRING(ITM_DES, 20)
MNOTEINVSELECT	Used in Data.Connect mass emailing
MONARCH	Used to generate a Monarch label format string
MONTHNAMES	Returns the month name relative to the current month	MONTHNAMES(0) = today's month MONTHNAMES(-1) = last month MONTHNAMES(+1) = next month
MONTHNAMESSHORT	Returns the short month names, e.g. MAR
MONTHNAMESYR	Returns the month name and year offset from the current month, e.g. January 2010	MONTHNAMESYR(offset)	MONTHNAMESYR(-1) will return last month's month/year
MONTHNUMBER
MONTHSALES	Returns the current month's sales from last year for a debtor		MONTHSALES(DBT_LYSALES0, 5)
MSAFTIME	Formats a time
NEGATE	Reverses signs		NEGATE(1) gives the value -1
NEWBUSFUNC	Used to generate the New Business Report in POS	NEWBUSFUNC(nb_type) where nb_type can be: O = new order B = edited order Q = quote X = quote to order I = goods invoiced C = credits H = held orders J = held to order M = cost change on order N = sell change on order	NEWBUSFUNC(O) returns new orders
NUMCENTS	Returns the cents column as a number	NUMCENTS(amount)	NUMCENTS(6510432.10) returns 10
NUMHUNDREDS	Returns the hundreds column as a number	NUMHUNDREDS(amount)	NUMHUNDREDS(6510432.10) returns 4
NUMHUNDTHOUS	Returns the hundred thousands column as a number	NUMHUNDTHOUS(amount)	NUMHUNDTHOUS(6510432.10) returns 5
NUMMILLION	Returns the millions column as a number	NUMMILLION(amount)	NUMMILLION(6510432.10) returns 6
NUMTENS	Returns the tens column as a number	NUMTENS(amount)	NUMTENS(6510432.10) returns 3
NUMTENTHOUS	Returns the ten thousands column as a number	NUMTENTHOUS(amount)	NUMTENTHOUS(6510432.10) returns 1
NUMTHOUSANDS	Returns the thousands column as a number	NUMTHOUSANDS(amount)	NUMTHOUSANDS(6510432.10) returns 0
NUMUNITS	Returns the units column as a number
ORDAMTREMAINING	Returns the value of an order still to be invoiced
ORDWV	Calculates the weight or volume of an invoice	ORDWV(0) = weight ORDWV(1) = volume
PACKS	Returns the total number of packets (used of packs per carton items)		PACKS(DIL_QTYDEL)
PARTIALBLANK
PARTIALCOMP
PAYMETHOD	Returns the payment method for cash receipts		PAYMENTMETHOD(3) returns the name of the third payment method
PAYTOSTRING	Returns the amount in words, e.g. four hundred and fifty seven dollars and six cents	PAYTOSTRING(amount)	PAYTROSTRING(100) returns "one hundred dollars"
POSNUMBERBAR	Returns the sales value during the day on a register
POSPAYMETHOD	Returns a string with all the payment types used in the current POS docket, separated by commas
POSVALUELENGTH	Returns the sales budget for a Salesperson Commission Report
PPC	Returns the packs per carton figure from a packs and PPC value, e.g. given pack = 101 and ppc = 10 returns 10:01		PPC(CALC1, ITM_PPC)
PRICENAME	Returns the default price name for a debtor	PRICENAME(dbt_defprice,dbt_prgroup)	PRICENAME(DBT_DEF_PRICE,DBT_PRGROUP)
PRICESELECTED
PRICEWITHDISC	Returns the sell price including discounts. Used on purchase orders.
PURCHASEINFO	Returns either the next delivery date, the quantity of the next delivery or the total value on order for a job	PURCHASEINFO(ITM_NO, x) where x = 0 for next delivery date x = 1 for next delivery qty x = 2 for ex tax value of job orders x = 3 for inc tax value of job orders
PURCHASEUNITS	Calculates the final purchase units amount	PURCHASEUNITS(value, pu)	PURCHASEUNITS(ITM_PUNIT_QTY, ITM_PUNITS)
QTYONTHISORDER	Returns the quantity delivered on this order
RIGHTPADDTEXT	Right pad string
SAFEREORDPNT	Calculates the reorder point (optimal stock holding) for the Safety Stock Report
SAFESUGGACT	Returns the suggested action for the Safety Stock Report
SAFESUGGQTY	Returns the suggested quantity for the Safety Stock Report
SALESBUDGETCALC	Returns the sales budget for the Commission Report
SALESTAX
SERIALRANGE	Returns serial numbers as a range, e.g. 12345-12445
SHORTMONTHNAMESYR
SHORTPERIODNAMES	Returns the GL period in a short form, e.g. Jan 09	SHORTPERIODNAMES(period number)
STRCAT	Adds two strings together		STRCAT(S1,S2) adds S2 to S1
STRCATREV
STRHUNDREDS	Returns the hundreds column as a string	STRHUNDREDS(amount)	STRHUNDREDS(6510432.10) returns "Four"
STRHUNDTHOUS	Returns the hundred thousands column as a string	STRHUNDTHOUS(amount)	STRHUNDTHOUS(6510432.10) returns "Five"
STRIP	Removes trailing spaces from a string
STRMILLION	Returns the millions column as a string	STRMILLION(amount)	STRMILLION(6510432.10) returns "Six"
STRTENS	Returns the tens column as a string	STRTENS(amount)	STRTENTHOUS(6510432.10) returns "Three"
STRTENTHOUS	Returns the ten thousands column as a string	STRTENTHOUS(amount)	STRTENTHOUS(6510432.10) returns "One"
STRTHOUSANDS	Returns the thousands column as a string	STRTHOUSANDS(amount)	STRTHOUSANDS(6510432.10) returns "Zero"
STRUNITS	Returns the units column as a string	STRUNITS(amount)
SUBDAY	Calculates the number of days between two dates (subtracts one from the other)	SUBDAY(d1,d2)
SUBTOTAL	Totals an invoice at the bottom of the page, excluding any tax or freight. Can be used on purchase orders.
SUMM	Calculates the sum of an array of numbers		SUMM(DBT_COST0,12) calculates the sum of the last 12 months of sales costs for a debtor
SUMWARE
SUPPLIERITEMPART	Returns the supplier part number or the item number if no supplier part number exists	SUPPLIERPART(item, supplier)	SUPPLIERITEMPART(DIL_ITMNO, POH_SUPNO)
SUPPLIERPART	Returns the supplier part number		SUPPLIERPART(POL_ITMNO, POH_SUPNO)
SURCHARGEPERITEM	Returns the $ surcharge value per item. Can be used on purchase orders.
TAXPERITEM	Returns the $ tax value per item. Can be used on purchase orders.
TAXTOTAL	Returns the tax total on the bottom of an invoice. Can be used on purchase orders.
THISWEEK	Returns the week number for a given date	THISWEEK(date)
TIMEDIFF	Returns the time difference in seconds between two dates	TIMEDIFF(d1,d2)
TIMEFMT	Returns the time component of a date field	TIMEFMT(t)	TIMEFMT(DTRN_DATE)
TIMEHRSFMT	Returns the time in 12 hour format	TIMEHRSFMT(time)
TOTALTRANSFERAMT	Returns the total quantity to transfer
UNITPRICEEX	Returns the unit sell price excluding GST
UNITPRICEINC	Returns the unit sell price including GST
WETPERITEM	Returns the WET tax per unit sell
WETTOT	Returns the total amount of WET tax
WITMONHAND	Returns the on hand quantity from the Warehouse Item file
WORKINGDAYS	Returns the number of working days between two dates, including the hours, minutes and seconds	WORKINGDAYS(DIH_DATE, DIH_DELDATE) WORKINGDAYS(DIH_DATE, TODAY)	The return result is X Days HH:MM:SS, e.g. if the two dates used in the function are 16/01/3013 04:41:10PM and 29/01/2013 10:30:10AM, the return result is 8 Days 17:48:59. The TODAY parameter returns the number of working days up until now.
YTDSALES	Returns the year to date sales from last year for a debtor		YTDSALES(5)
ZEROTIME	Returns TRUE if the time is zero	ZEROTIME(date)

ALLOCATETO

Returns a list of POS orders, backorders or jobs that this order is allocated to

ALLREORDPNT

Calculates the optimal stock holding for Understock/Overstock Reports

ALLSUGGACT

Returns the suggested action for Understock/Overstock Reports

ALLSUGGQTY

Returns the suggested quantity for Understock/Overstock Reports

AMTTOPAY

Returns the amount to pay in the Creditors Payment Report

AUTOLINEREF

BASEPRICEEX

Returns the pre-discount pre-tax price (the base selling price exclusive of GST and discounts). This is useful if you have an inc GST price and want to see the ex GST price before discount.

BC3OF9

Returns a string prefixed with an asterisk (*) for use with a type 3 of 9 barcode

BPAYREFERENCE

Returns a valid BPAY reference number

DBT_BPAYREF

INVOICENO - optional depending on whether Micronet is set to use the invoice number when generating BPay references

BPAYREFERENCE(DBT_BPAYREF, INVOICENO)

BUDGETVAL

Returns the correct budget value for a particular month index

BUDGETVAL(amt, index)

BUDGETVAL(DBUD_SALESBUD0, 0)

BUDGETYTD

Calculates the year to date value of a budget figure, based on the current date

BUDGETYTD(DBUD_SALESBUD0)

BUDTHISMONTH

Returns the salesperson's budget for the current month

BUDTHISMONTH()

CALCCOST

Calculates the cost of an item (cost x quantity) taking into account packs per carton and purchase units

CALCCOST(cost, qty)

CALCOST(DIL_QTYDEL, DIL_COST)

CALCDISCOUNT

Calculates the discount on the Creditors Payment Due Report

CALCPURCHASEQTY

Calculates the purchase quantity taking into account purchasing units

CALCPURCHASEQTY(PHL_QTYORD)

CARTONS

Returns the total number of cartons (used of packs per carton items)

CARTONS(DIL_QTYDEL)

CHQAMT

Returns the cheque total for the cheque returned by CHQNO

CHQAMT(i)

CHQNO

Returns a list of cheque numbers used on a statement

CHQNO(i)

CHQNO(0) is the first cheque, CHQNO(1) is the second cheque, etc.

CODABAR

Returns a CODABAR formatted barcode

CODABAR(s)

COMM

Calculates a salesperson's commission on the Commission Report

COMM(DTRN_SALES)

CONTAINS

Returns TRUE if a field contains a string

CONTAINS(S1,S2) returns 1 when S2 is contained in S1

CONTRACT

Returns the contract information for a purchase order

CONTRACT(1..19)

CONTRACT(1..5) = Discounts 1..5

CONTRACT(6/7) = Rebates 1 or 2

CONTRACT(8) = Settlement Discount

CONTRACT(9) = Total Discounts

CONTRACT(11..15) = Discounts 1..5 accumulated for purchase order

CONTRACT(16/17) = Rebates 1 or 2 accumulated for purchase order

CONTRACT(18) = Settlement Discount

CONTRACT(19) = Total discounts accumulated for purchase order

COURIERBOXBC

Specialised BOX courier format

CURNO

Breaks up the current table for the ATB in MDS and MCL

CURNO(i)

CURNO(0) returns the first exchange rate found in the debtors/creditors transaction file

DATEDUE

Calculates the due date on the Creditors Payment Report and the due date for an MDS invoice

DATEFMT

Returns the number as a date in DD/MM/YY format

DEBTDUEPERIOD

Returns the current month and year in abbreviated format, e.g. MAR 2010

DEBTORITEMPART

Returns the debtor's part number or the item number if no debtor part exists

DEBTORITEMPART(DIL_ITMNO, DOH_DBTNO)

DEBTORPART

Returns the debtor's part number

DEBTORPART(DIL_ITMNO, DOH_DBTNO)

DEBTPER30DAYS

Returns the debtor's period number based on 30 day periods

DEBTPERIOD

Calculates the debtor's period number based on today's date and the invoice date

DEBTPERTERM

Returns the debtor's period number based on the debtor's sales terms (rather than invoice terms)

DELAMTREMAINING

Returns the amount remaining to be paid on an order

DELIVERYADDRESS

Returns the delivery address for a line of an order

DISCPERITEM

Within an invoicing operation, returns the discount that a customer receives ($ value each). Can be used on purchase orders.

DREORDPNT

Returns the reorder point calculated for the Dead Stock Report

DSUGGACT

Returns the suggested action for the Dead Stock Report

DSUGGQTY

Returns the suggested quantity for the Dead Stock Report

DUTYEXPERITEM

Returns the duty and excise per unit sell

DUTYEXTOT

Returns the total amount of duty and excise

EAN13

Returns a barcode in EAN13 format. Works only with the specific EAN13 barcode font supplied by Micronet (ean-13.ttf). You must assign the barcode font to the calculation field on the report. If the EAN13 barcode only has 12 characters, a check digit will be added.

EAN13(s)

EAN13(ITM_BC)

EANCHECKDIGIT

Returns a list of assigned bin locations

ELAPSEDTIMEFMT

Calculates and formats the elapsed time

ELAPSEDTIMEFMT(secs)

EORDERSTATUS

Returns the electronic order status

FREESTOCK

Returns the free stock amount of an item

FREESTOCK(ITM_ONHAND)

GETCOMP

Returns a field from the current company

GETCOMP(f)

GETCOMP(COMP_NAME) returns the current company's name

GETCOMP(COMP_FIFOPERITEM)

GETCOMP(XC_INVOICE0)

GETCUSTDEBT

GETDEBTD

Returns a field from a debtor

GETDEBTD(DTRN_DBTSALE, DBTNAME)

GETDELIVERYOPTIONS

Returns the name of the delivery option from enPOS

GETDELIVERYOPTIONS(posl_delopt)

GETDEPTNAME

Returns the current department name

GETDEPTNAME(GLACC_ACCOUNT)

GETDUTY

Returns the duty percentage

GETDUTY(ITM_DUTYRATE)

GETGLMONTHNAMES

Returns the month name from the current month, e.g. OCTOBER

GETGLMONTHNAMESYR

Returns the month name and year from the current month, e.g. OCTOBER 2010

GETGLPERIODNAME

Returns the GL period name from the base GL period number, e.g. Aug 04

GETGLPERIODNAME(PeriodNo)

GETITEMD

GETITEMFIELD

GETITEMUNIT1

Returns the first tiles/boxes for the item and value passed in

GETITEMUNIT2

Returns the second tiles/boxes for the item and value passed in

GETITEMUNIT3

Returns the third tiles/boxes for the item and value passed in

GETMONTHNAME

Returns the month name offset from the current month

GETMONTHNAME(n)

GETMULTIBINS

Returns a list of bin locations

GETMULTIBINS(DIL_SERLINK)

GETMULTIBINTEXT

Returns the specified text line related to multi bin picking slips in bin order

GETMULTIBINTEXT(DIL_SERLINK, #) where # is the number of the text line (i.e. 1 for first text line after item line, 2 for second text line, etc)

GETMULTIBINTEXT(DIL_SERLINK, 1); GETMULTIBINTEXT(DIL_SERLINK, 2)
would print both text lines if there were two text lines related to one item line.

If there is only one text line, the second text line will not print.

GETNARRATION

Gets a narration from the narration file

GETPERIODNAME

Returns the period name from the start of the financial year

GETPERIODNAME(0) returns the period name of the first financial period of the year

GETPERIODVALUE

Returns the period value from the start of the financial year

GETPERIODVALUE(DBT_SALE0, 0) gets the debtor sales value for the first period of the financial year

GETRATENAME

Returns the overtime rate name

GETRATENAME(r)

GETRATENAME(JT_SELECT)

GETSQLFIELD

GETSTAX

Returns the tax percentage. Looks at the system parameters to see if tax tables are turned on.

GETSTAX(ITM_STAX, ITM_STAXPERCENT)

GETUSER

Returns a field for the current user ID

GETUSER(f)

GETUSER(USERID_SUPERVISOR)

GETWEEKNUMBER

Returns the week number of the year based on the date

GETWEEKNUMBER(date)

GETWITM

Returns a value from the Warehouse Item file

GETWITM(itm,wh,field)

GETWITM("123","W1",WITM_ONHAND)

GLISCURPL

Returns TRUE if this account is the current year Profit and Loss account

GLISUNAPPPY

Returns TRUE if this account is the unappropriated Profit and Loss account

GLLYBALANCE

Returns last year's GL balance amount

GLLYBALANCETOPER

Returns last year's GL balance up to the current period

GLLYBUDGET

Returns last year's GL budget amount

GLLYBUDGETTOPER

Returns last year's GL budget up to the current period

GLLYMTDBALANCE

Returns the month to date GL balance for the current period last year

GLMTDBALANCE

Returns the month to date GL balance amount for the current period

GLMTDBUDGET

Returns the month to date GL budget amount for the current period

GLMTDPERCENT

Returns the month to date GL percentage value in the General Ledger profit and loss

GLOPENBALANCE

Returns the opening balance for the selected period in GL Account Inquiry reports

GLVALUEPERIOD

Returns the GL value for a given period

GLVALUEPERIOD(n)

GLYTDBALANCE

Returns the GL balance amount for the year to date

GLYTDBUDGET

Returns the GL budget amount for the year to date

GLYTDPERCENT

Returns the year to date GL percentage value in the General Ledger profit and loss

GP

Returns the gross profit as a percentage

GP(sale, cost)

GPREVERSE

Returns the sell price based on the gross profit percentage and cost

GPREVERSE(gp, cost)

GrandTotal

Returns the total invoice amount including subtotal, tax, freight, additional charges, etc.

GSTPERIOD

Returns the GST period name

GSTPERIOD(GST_PERIOD)

IMPORTCOST

Returns import costing information for the Import Costing Report

IMPORT COST(1..28)

IMPORT COST(1) = shipment base

IMPORT COST(2) = price in $AUS

IMPORT COST(3) = price in $AUS at duty ex rate

IMPORT COST(4) = duty in $AUS

IMPORT COST(5) = value used to appropriate expenses (e.g. weight, volume or price)

IMPORT COST(6) = item value used in appropriation

IMPORT COST(7) = extra charges in $AUS

IMPORT COST(8) = tax base

IMPORT COST(9) = sales tax

IMPORT COST(10) = landed cost

IMPORT COST(11) = loading

IMPORT COST(12) = total shipment value

IMPORT COST(13) = total value in $AUS

IMPORT COST(14) = total value in $AUS at duty ex rate

IMPORT COST(15) = total duty

IMPORT COST(16) = total value used to appropriate expenses

IMPORT COST(17) = total value used in appropriation

IMPORT COST(18) = total extra charges

IMPORT COST(19) = total tax base

IMPORT COST(20) = total sales tax

IMPORT COST(21) = total landed cost

IMPORT COST(22) = total loading

IMPORT COST(23) = price in $AUS per each

IMPORT COST(24) = duty in $AUS per each

IMPORT COST(25) = extra charges in $AUS per each

IMPORT COST(26) = sales tax in $AUS per each

IMPORT COST(27) = landed cost per each

IMPORT COST(28) = loading per each

IMPORT COST(29) = total charges converted to $AUS

INCGST

Converts amount to including GST

INCGST(DIH_FREIGHT)

JCCOSTCENTER

KITEXPANDEDQTY

Calculates the expanded quantity of a multi level kit component

LEFTSTRING

Returns the first n characters of a string

LEFTSTRING(s, n)

LEFTZEROFILL

Zero fill string to the left

LEVY

Returns the levy amount for an invoice or line item. Can be used on purchase orders.

Accepts a single parameter - either 0 or 1.

LEVY(0) = total levy for an invoice

LEVY(1) = total levy for a particular line item

LEVY(0)

LEVY(1)

LONGPERIODNAMES

Returns the GL period in a long format, e.g. January 2010

LONGPERIODNAMES(period number)

MAINTCONTRACTEXP

Returns the expiry date for a maintenance contract

MIDSTRING

Returns the ith character of s and then the rest of the string

MIDSTRING(s, i)

MIDSTRING(ITM_DES, 20)

MNOTEINVSELECT

Used in Data.Connect mass emailing

MONARCH

Used to generate a Monarch label format string

MONTHNAMES

Returns the month name relative to the current month

MONTHNAMES(0) = today's month

MONTHNAMES(-1) = last month

MONTHNAMES(+1) = next month

MONTHNAMESSHORT

Returns the short month names, e.g. MAR

MONTHNAMESYR

Returns the month name and year offset from the current month, e.g. January 2010

MONTHNAMESYR(offset)

MONTHNAMESYR(-1) will return last month's month/year

MONTHNUMBER

MONTHSALES

Returns the current month's sales from last year for a debtor

MONTHSALES(DBT_LYSALES0, 5)

MSAFTIME

Formats a time

NEGATE

Reverses signs

NEGATE(1) gives the value -1

NEWBUSFUNC

Used to generate the New Business Report in POS

NEWBUSFUNC(nb_type) where nb_type can be:

O = new order

B = edited order

Q = quote

X = quote to order

I = goods invoiced

C = credits

H = held orders

J = held to order

M = cost change on order

N = sell change on order

NEWBUSFUNC(O) returns new orders

NUMCENTS

Returns the cents column as a number

NUMCENTS(amount)

NUMCENTS(6510432.10) returns 10

NUMHUNDREDS

Returns the hundreds column as a number

NUMHUNDREDS(amount)

NUMHUNDREDS(6510432.10) returns 4

NUMHUNDTHOUS

Returns the hundred thousands column as a number

NUMHUNDTHOUS(amount)

NUMHUNDTHOUS(6510432.10) returns 5

NUMMILLION

Returns the millions column as a number

NUMMILLION(amount)

NUMMILLION(6510432.10) returns 6

NUMTENS

Returns the tens column as a number

NUMTENS(amount)

NUMTENS(6510432.10) returns 3

NUMTENTHOUS

Returns the ten thousands column as a number

NUMTENTHOUS(amount)

NUMTENTHOUS(6510432.10) returns 1

NUMTHOUSANDS

Returns the thousands column as a number

NUMTHOUSANDS(amount)

NUMTHOUSANDS(6510432.10) returns 0

NUMUNITS

Returns the units column as a number

ORDAMTREMAINING

Returns the value of an order still to be invoiced

ORDWV

Calculates the weight or volume of an invoice

ORDWV(0) = weight

ORDWV(1) = volume

PACKS

Returns the total number of packets (used of packs per carton items)

PACKS(DIL_QTYDEL)

PARTIALBLANK

PARTIALCOMP

PAYMETHOD

Returns the payment method for cash receipts

PAYMENTMETHOD(3) returns the name of the third payment method

PAYTOSTRING

Returns the amount in words, e.g. four hundred and fifty seven dollars and six cents

PAYTOSTRING(amount)

PAYTROSTRING(100) returns "one hundred dollars"

POSNUMBERBAR

Returns the sales value during the day on a register

POSPAYMETHOD

Returns a string with all the payment types used in the current POS docket, separated by commas

POSVALUELENGTH

Returns the sales budget for a Salesperson Commission Report

PPC

Returns the packs per carton figure from a packs and PPC value, e.g. given pack = 101 and ppc = 10 returns 10:01

PPC(CALC1, ITM_PPC)

PRICENAME

Returns the default price name for a debtor

PRICENAME(dbt_defprice,dbt_prgroup)

PRICENAME(DBT_DEF_PRICE,DBT_PRGROUP)

PRICESELECTED

PRICEWITHDISC

Returns the sell price including discounts. Used on purchase orders.

PURCHASEINFO

Returns either the next delivery date, the quantity of the next delivery or the total value on order for a job

PURCHASEINFO(ITM_NO, x) where

x = 0 for next delivery date

x = 1 for next delivery qty

x = 2 for ex tax value of job orders

x = 3 for inc tax value of job orders

PURCHASEUNITS

Calculates the final purchase units amount

PURCHASEUNITS(value, pu)

PURCHASEUNITS(ITM_PUNIT_QTY, ITM_PUNITS)

QTYONTHISORDER

Returns the quantity delivered on this order

RIGHTPADDTEXT

Right pad string

SAFEREORDPNT

Calculates the reorder point (optimal stock holding) for the Safety Stock Report

SAFESUGGACT

Returns the suggested action for the Safety Stock Report

SAFESUGGQTY

Returns the suggested quantity for the Safety Stock Report

SALESBUDGETCALC

Returns the sales budget for the Commission Report

SALESTAX

SERIALRANGE

Returns serial numbers as a range, e.g. 12345-12445

SHORTMONTHNAMESYR

SHORTPERIODNAMES

Returns the GL period in a short form, e.g. Jan 09

SHORTPERIODNAMES(period number)

STRCAT

Adds two strings together

STRCAT(S1,S2) adds S2 to S1

STRCATREV

STRHUNDREDS

Returns the hundreds column as a string

STRHUNDREDS(amount)

STRHUNDREDS(6510432.10) returns "Four"

STRHUNDTHOUS

Returns the hundred thousands column as a string

STRHUNDTHOUS(amount)

STRHUNDTHOUS(6510432.10) returns "Five"

STRIP

Removes trailing spaces from a string

STRMILLION

Returns the millions column as a string

STRMILLION(amount)

STRMILLION(6510432.10) returns "Six"

STRTENS

Returns the tens column as a string

STRTENS(amount)

STRTENTHOUS(6510432.10) returns "Three"

STRTENTHOUS

Returns the ten thousands column as a string

STRTENTHOUS(amount)

STRTENTHOUS(6510432.10) returns "One"

STRTHOUSANDS

Returns the thousands column as a string

STRTHOUSANDS(amount)

STRTHOUSANDS(6510432.10) returns "Zero"

STRUNITS

Returns the units column as a string

STRUNITS(amount)

SUBDAY

Calculates the number of days between two dates (subtracts one from the other)

SUBDAY(d1,d2)

SUBTOTAL

Totals an invoice at the bottom of the page, excluding any tax or freight. Can be used on purchase orders.

SUMM

Calculates the sum of an array of numbers

SUMM(DBT_COST0,12) calculates the sum of the last 12 months of sales costs for a debtor

SUMWARE

SUPPLIERITEMPART

Returns the supplier part number or the item number if no supplier part number exists

SUPPLIERPART(item, supplier)

SUPPLIERITEMPART(DIL_ITMNO, POH_SUPNO)

SUPPLIERPART

Returns the supplier part number

SUPPLIERPART(POL_ITMNO, POH_SUPNO)

SURCHARGEPERITEM

Returns the $ surcharge value per item. Can be used on purchase orders.

TAXPERITEM

Returns the $ tax value per item. Can be used on purchase orders.

TAXTOTAL

Returns the tax total on the bottom of an invoice. Can be used on purchase orders.

THISWEEK

Returns the week number for a given date

THISWEEK(date)

TIMEDIFF

Returns the time difference in seconds between two dates

TIMEDIFF(d1,d2)

TIMEFMT

Returns the time component of a date field

TIMEFMT(t)

TIMEFMT(DTRN_DATE)

TIMEHRSFMT

Returns the time in 12 hour format

TIMEHRSFMT(time)

TOTALTRANSFERAMT

Returns the total quantity to transfer

UNITPRICEEX

Returns the unit sell price excluding GST

UNITPRICEINC

Returns the unit sell price including GST

WETPERITEM

Returns the WET tax per unit sell

WETTOT

Returns the total amount of WET tax

WITMONHAND

Returns the on hand quantity from the Warehouse Item file

WORKINGDAYS

Returns the number of working days between two dates, including the hours, minutes and seconds

WORKINGDAYS(DIH_DATE, DIH_DELDATE)

WORKINGDAYS(DIH_DATE, TODAY)

The return result is X Days HH:MM:SS, e.g. if the two dates used in the function are 16/01/3013 04:41:10PM and 29/01/2013 10:30:10AM, the return result is 8 Days 17:48:59.

The TODAY parameter returns the number of working days up until now.

YTDSALES

Returns the year to date sales from last year for a debtor

YTDSALES(5)

ZEROTIME

Returns TRUE if the time is zero

ZEROTIME(date)